[Looker]テンプレートフィルタをネイティブ派生テーブルに適用してみた #looker

「カスタマーストーリー」を支えるツール特集

#Looker

さがら

2021.03.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

さがらです。

少し前になりますが、Lookerのテンプレートフィルタに関してブログを投稿しておりました。

もしこのブログを読まれた方がいましたら、「あれっ、ネイティブ派生テーブルではテンプレートフィルタ使えないの？」と思われた方もいるかもしれません。特にネイティブ派生テーブルには触れていなかったですからね…

実際には、ネイティブ派生テーブルにもテンプレートフィルタは使えます。

このブログでは、ネイティブ派生テーブルに対してテンプレートフィルタを適用させることを試してみたので、その内容をまとめてみたいと思います。

前提条件

このブログで検証したいことと、その前提条件を説明します。

実施する内容としては、「country＆city別に売上の合計値を求め、売上の上位順にランキングをつけるネイティブ派生テーブルに対して、ユーザーがExplore上で選択した国の中での売上順にランキングをつける」ということをやってみます。

この内容自体は、前回のテンプレートフィルタブログの"事例"と同じ内容となります。同じ内容の方が比較しやすいと思いますので…

まず、ネイティブ派生テーブルなので、事前にorder_items.viewとusers.viewをJOINした、order_items_add_userinfoという名称でExploreを下記のように定義しておきます。

explore: order_items_add_userinfo {
  from: order_items
  join: users {
    type: left_outer
    sql_on: ${order_items_add_userinfo.user_id} = ${users.id} ;;
    relationship: many_to_one
  }
}

このExplore上で「country」と「city」と、売上の合計値を算出する「total_Sales」を選択したネイティブ派生テーブルのLookMLコードを取得し、加えてderived_columnパラメータを使ってWINDOW関数を用いたランキングを取得するカラムを定義します。

そうすると、テンプレートフィルタ適用前のネイティブ派生テーブルのLookMLでの定義は下記のようになります。

view: ranking_by_city_country_ndt {
  derived_table: {
    explore_source: order_items_add_userinfo {
      column: country { field: users.country }
      column: city { field: users.city }
      column: total_sales {}
      derived_column: ranking {
        sql: RANK() OVER (ORDER BY total_sales DESC) ;;
      }
    }
  }
  dimension: country {
    type: string
  }
  dimension: city {
    type: string
  }
  dimension:ranking {
    type: number
  }
  measure: total_sales {
    value_format: "$#,##0.00"
    type: number
  }
}

ちなみに、このネイティブ派生テーブルに何もフィルタをかけずにランキングを確認すると、下図の通りUSAのCityが独占しております。

データ上はUKのCityもあるのですが、UKだけに絞ってランキングを出すと、一番良い順位でも500位以下となっております。

次章から、このネイティブ派生テーブルに対してテンプレートフィルタを適用し、ユーザーがExplore上で選択した国の中での売上順にランキングをつけられるようにする方法を「2種類」説明していきます。

bind_filtersを使った実装

まず１つ目の方法が、bind_filtersというパラメータを用いた方法です。

bind_filtersはネイティブ派生テーブルにのみ使用可能なパラメータで、「指定したフィルターの絞り込み条件を、指定したネイティブ派生テーブル内のフィールドに適用させることが出来る」パラメータです。

手順1.filterの定義

まずはSQL派生テーブルのときと同様に、filterパラメータをネイティブ派生テーブルを定義しているviewの中で定義する必要があります。

内容としては下記のようなコードになります。

オプションとして、出来る限りsuggest_dimensionとsuggest_exploreは設定し、このフィルタを使用するときに候補値が出るようにしてあげると尚良いと思います。

filter: filter_country {
    type: string
    suggest_dimension: country
    suggest_explore: order_items_add_userinfo
  }

手順2.bind_filtersの定義

次に、bind_filtersパラメータの実装になります。

bind_filtersは、derived_tableパラメータ内のexplore_sourceサブパラメータ内で定義します。

先に書き方の例を示します。

bind_filters: {
  from_field: ranking_by_city_country_ndt.filter_country
  to_field: users.country
}

定義するときの特徴としては、from_fieldとto_field、2つのサブパラメータを定義してあげる必要があります。

from_fieldには、テンプレートフィルタとして機能させるフィルタを指定します。ここでは、先程手順１で作成したfilterの名称を指定すればOKです！１つポイントとしては「view名.filter名」と指定しないと、フィルタが認識されずエラーを起こしてしまうため注意しましょう。

to_fieldには、from_fieldで指定したフィルタを適用させる、ネイティブ派生テーブル内のフィールド名を指定します。こちらもfrom_fieldと同様に、「view名.filter名」で指定しないと、フィールドが認識されずエラーとなってしまうので注意しましょう。

実装後の全体像

手順１&手順２の内容を実装後のネイティブ派生テーブルのLookMLのコードは、下記のようになりました。

view: ranking_by_city_country_ndt {
  derived_table: {
    explore_source: order_items_add_userinfo {
      column: country { field: users.country }
      column: city { field: users.city }
      column: total_sales {}
      derived_column: ranking {
        sql: RANK() OVER (ORDER BY total_sales DESC) ;;
      }
      # 手順２で実装したbind_filters
      bind_filters: {
        from_field: ranking_by_city_country_ndt.filter_country
        to_field: users.country
      }
    }
  }
  dimension: country {
    type: string
  }
  dimension: city {
    type: string
  }
  dimension:ranking {
    type: number
  }
  measure: total_sales {
    value_format: "$#,##0.00"
    type: number
  }
  # 手順１で実装したfilter
  filter: filter_country {
    type: string
    suggest_dimension: country
    suggest_explore: order_items_add_userinfo
  }
}

Explore上で試してみる

では、この実装した内容をExplore上で使ってみます。

まずフィールドピッカーから、新しく作成したフィルター「filter_country」を選択します。

続いて、このフィルタに対して、国名を絞らないと500位以下のCityしかなかった「UK」で絞り込みを行います。

すると、UK内のCityで１位から順番にランキングが作られました！

これでネイティブ派生テーブルにもテンプレートフィルタが適用されていることが確認できましたね。

bind_all_filtersを使った実装

２つ目の方法は、bind_all_filtersというパラメータを用いた方法です。

bind_all_filtersもネイティブ派生テーブルにのみ使用可能なパラメータなのですが、「派生元のExploreのフィールドを使ってフィルタを設定すると、対象のネイティブ派生テーブルに対してもそのフィルタが適用される」パラメータです。（※派生元のExploreとのJOIN前提の機能です。）

文章だけだと分かりづらいかと思うので、手順と実例を合わせて見ていきましょう。

手順1.bind_all_filtersの定義

まずderived_tableパラメータ内のexplore_sourceサブパラメータ内で、bind_all_filters: yesと入れます。

元のネイティブ派生テーブルの編集はこれだけです！以下に改めてbind_all_filters実装後のネイティブ派生テーブルのコードを載せておきます。

view: ranking_by_city_country_ndt {
  derived_table: {
    explore_source: order_items_add_userinfo {
      column: country { field: users.country }
      column: city { field: users.city }
      column: total_sales {}
      derived_column: ranking {
        sql: RANK() OVER (ORDER BY total_sales DESC) ;;
      }
      # bind_all_filtersを追加
      bind_all_filters: yes
    }
  }
  dimension: country {
    type: string
  }
  dimension: city {
    type: string
  }
  dimension:ranking {
    type: number
  }
  measure: total_sales {
    value_format: "$#,##0.00"
    type: number
  }
}

手順2.元のexploreに対して、ネイティブ派生テーブルをJOINする

次に、ネイティブ派生テーブルの元のExploreに対して、bind_all_filtersを追加したネイティブ派生テーブルのviewをJOINさせます。

今回の例では、元のExploreはorder_items_add_userinfoという名称です。このExploreに対して、作成したネイティブ派生テーブルのviewであるranking_by_city_country_ndtをJOINさせます。

JOINさせた後のorder_items_add_userinfoの定義は下記のようになります。

explore: order_items_add_userinfo {
  from: order_items
  join: users {
    type: left_outer
    sql_on: ${order_items_add_userinfo.user_id} = ${users.id} ;;
    relationship: many_to_one
  }
  join: ranking_by_city_country_ndt {
    type: left_outer
    sql_on: ${users.country} = ${ranking_by_city_country_ndt.country} AND ${users.city} = ${ranking_by_city_country_ndt.city};;
    relationship: many_to_one
  }
}

Explore上で試してみる

では実装したExploreを試して、どうテンプレートフィルタが作用するのか見てみましょう。

まず使用するExploreについてですが、ネイティブ派生テーブルの派生元のExploreを使用しなければいけません。始めにこの点をご注意ください。

次に、任意のフィールドを選択した上で、フィルタも設定します。

ここで重要なのは、フィルタに選択するのは、派生元のExploreで定義されているフィールドでないと、bind_all_filtersによるテンプレートフィルタが機能しません。

下図の場合、ネイティブ派生テーブルはranking_by_city_country_ndt.viewで定義しているため、こちらのcountryをフィルタに選んでしまいがちですが、これだとテンプレートフィルタが機能しません。派生元のExploreで使用されているusers.viewのcountryをフィルタに設定しましょう。

ここまで出来たら、任意の国をフィルタ欄から選んで実行することで、選択した国の中でランキングが作られます！

更に、bind_all_filtersならではのメリットがあります。それは、派生元のExploreのどのディメンション・メジャーをフィルタにかけても、ネイティブ派生テーブルの内容が動的に切り替わるということです。

試しに、ランキングの算出に用いているtotal_salesというメジャーをフィルタにかけて、ランキングがどう変化するのかを見てみましょう。

まず何もフィルタをかけていない場合、ランキングとtotal_salesは下図の様な状態だったとします。トップ４のCityだけ、total_salesが100000を超えていますね。

ここで、このトップ４のCityを結果から除外すべく、「total_salesが100000未満の値」でフィルタを設定してみます。実際のフィルタは下図の内容になります。

すると、100000以上の値を持ったトップ4のCityが表示されなくなった上で、ネイティブ派生テーブル上で定義しているランキングの内容も動的に変化することが確認できました！

bind_all_filtersを一つ設定して元のExploreにJOINするだけで、元のExploreのどのフィールドを用いてフィルタを行ってもRANK()などのWINDOW関数が動的に機能するということは、LookMLの記述量が大幅に減るだけでなく、ビジネスユーザーにとってもより幅の広い分析を容易に可能にすると思います。とても魅力的な機能ではないでしょうか！

注意点

一見テンプレートフィルタを簡単に適用できて良い面しかないように思えるbind_all_filtersですが、注意点があります。

それはbind_all_filters:yesにした後に、そのネイティブ派生テーブルのviewを元のExploreとJOINせずに使用すると、下図のようにエラーが発生します。

このため、bind_all_filtersを適用したネイティブ派生テーブルは、派生元のExploreに対してJOINさせないと使えなくなってしまいます。この点だけご注意ください。

2つの方法の使い分け方

テンプレートフィルタをネイティブ派生テーブルに実装する方法として、bind_filtersを用いた方法、bind_all_filtersを用いた方法、この２種類についてそれぞれ説明しました。

これまでの説明だと「bind_all_filtersだけでいいじゃない？」と思われる方もいるかもしれませんが、そう簡単な話ではありません。

ということで、以下に私が思う使い分け方をまとめてみます。

bind_filtersを使った方が良い場合
- ネイティブ派生テーブルのview単体のExploreや、ネイティブ派生テーブルのviewと全く関係のないviewをJOINしたExploreとして使いたい時
- 派生元のExploreで絞り込んだ条件と同じ内容で、ネイティブ派生テーブルの絞り込みをしたくない時。例えば、派生元のExploreとネイティブ派生テーブルをJOINして分析したいとき、派生元のExploreでは2020年のデータで絞り込み、ネイティブ派生テーブルでは2019年のデータで絞り込んで、分析をしたい、という場合にはbind_all_filtersは使用できない。
bind_all_filtersを使った方が良い場合
- 上記の「bind_filtersを使った方がいい場合」に当てはまらなければ、基本的にはbind_all_filtersで良いと思います。